import hashlib
from flask import Flask, render_template, request, session, redirect, url_for, jsonify
import pandas as pd
import pymysql
from functools import wraps
from pyecharts import options as opts
from pyecharts.charts import Bar, Pie, Line
import seaborn as sns
import matplotlib.pyplot as plt

app = Flask(__name__)
app.secret_key = 'your_secret_key'

# MySQL数据库连接
db = pymysql.connect(host="localhost", port=3306, user="root", password="123456", db="周六满课", charset="utf8")
cur = db.cursor()


# 登录验证装饰器
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'username' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)

    return decorated_function


@app.route('/')
def login_redirect():
    # 重定向到登录页面
    return redirect("/login.html")


@app.route('/about.html', methods=["GET", 'POST'])
@login_required
def about():
    # Load all available data files
    cities = ['beijing', 'shanghai', 'shunde']
    years = ['2020', '2021', '2022', '2023']

    if request.method == 'POST':
        city = request.form['city']
        year = request.form['year']
        file_path = f"{city}{year}.csv"
        df = pd.read_csv(file_path, encoding='gbk',
                         names=['date', 'zldj', 'aqi', 'pm25', 'pm10', 'so2', 'no2', 'co', 'o3'])
        data = df.to_dict(orient='records')
    else:
        # Load default data
        df = pd.read_csv("北京2020.csv", encoding='gbk',
                         names=['date', 'zldj', 'aqi', 'pm25', 'pm10', 'so2', 'no2', 'co', 'o3'])
        data = df.to_dict(orient='records')

    return render_template("about.html", data=data, cities=cities, years=years)


@app.route('/login.html', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        hashed_password = hashlib.sha256(password.encode()).hexdigest()

        cur.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, hashed_password))
        user = cur.fetchone()

        if user:
            # 用户认证成功，设置 session 并重定向到 index.html 页面
            session['username'] = username
            return redirect("/index.html")
        else:
            # 用户认证失败，显示登录页面并显示错误消息
            return render_template("login.html", error="密码错误")
    else:
        return render_template("login.html")


@app.route('/register', methods=['POST'])
def register():
    username = request.form['username']
    password = request.form['password']
    hashed_password = hashlib.sha256(password.encode()).hexdigest()

    # 检查用户名是否已存在
    cur.execute("SELECT * FROM users WHERE username = %s", (username,))
    existing_user = cur.fetchone()
    if existing_user:
        # 如果用户名已存在，返回错误消息
        return render_template("login.html", error="用户名已存在，请选择其他用户名")

    # 检查密码是否已存在（可选）
    cur.execute("SELECT * FROM users WHERE password = %s", (hashed_password,))
    existing_password = cur.fetchone()
    if existing_password:
        # 如果密码已存在，返回错误消息
        return render_template("login.html", error="密码已存在，请选择其他密码")

    # 将用户名和密码插入数据库
    cur.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (username, hashed_password))
    db.commit()

    return redirect("/login.html")


@app.route('/index.html')
@login_required
def index():
    cur.execute("SELECT * FROM news1")
    data = cur.fetchall()
    return render_template("index.html", data=data, success_message="Login successful!")


@app.route('/portfolio.html', methods=["GET", 'POST'])
@login_required
def portfolio():
    if request.method == 'POST':
        city = request.form['city']
        year = request.form['year']
        file_path = f"{city}{year}.csv"
        df = pd.read_csv(file_path, encoding='gbk',
                         names=['date', 'zldj', 'aqi', 'pm25', 'pm10', 'so2', 'no2', 'co', 'o3'])
    else:
        city = "北京"
        year = 2020
        df = pd.read_csv("北京2020.csv", encoding='gbk',
                         names=['date', 'zldj', 'aqi', 'pm25', 'pm10', 'so2', 'no2', 'co', 'o3'])

    v = pd.to_datetime(df['date'], format='%Y/%m/%d')

    df.index = v

    def fun(x):
        return x.month

    m = df.groupby(fun)
    y1 = m["so2"].mean().round(2)
    y2 = m["no2"].mean().round(2)
    x = [str(i) + "月" for i in range(1, 13)]

    # 计算相关系数
    correlation_coefficient = df['so2'].corr(df['no2'])

    # 生成折线图
    c = (
        Bar(init_opts=opts.InitOpts(bg_color="#E6E6E6"))
        .add_xaxis(list(x))
        .add_yaxis("so2", list(y1))
        .add_yaxis("no2", list(y2))
        .set_global_opts(title_opts=opts.TitleOpts(title=f"{year}年{city}so2和no2值全年数据分析",
                                                   pos_left="center", pos_top="20px",
                                                   title_textstyle_opts={"color": "black"}),
                         legend_opts=opts.LegendOpts(pos_right="100", pos_top="20px"))
    )

    # 生成热力图
    plt.figure(figsize=(8, 6))
    sns.heatmap(df[['so2', 'no2']].corr(), annot=True, cmap='coolwarm', fmt=".2f")
    plt.title('SO2&NO2')
    plt.xlabel('SO2')
    plt.ylabel('NO2')
    heatmap_path = f"static/images/{city}{year}_heatmap.png"
    plt.savefig(heatmap_path)

    return render_template("portfolio.html", myechart=c.render_embed(), correlation_coefficient=correlation_coefficient,
                           heatmap_path=heatmap_path, city=city, year=year)


@app.route('/contact.html', methods=["GET", 'POST'])
@login_required
def contact():
    if request.method == 'POST':
        city = request.form['city']
        year = request.form['year']
        file_path = f"{city}{year}.csv"
        df = pd.read_csv(file_path, encoding='gbk',
                         names=['date', 'zldj', 'aqi', 'pm25', 'pm10', 'so2', 'no2', 'co', 'o3'])
    else:
        city = "北京"
        year = 2020
        df = pd.read_csv("北京2020.csv", encoding='gbk',
                         names=['date', 'zldj', 'aqi', 'pm25', 'pm10', 'so2', 'no2', 'co', 'o3'])
    m = df.groupby("zldj").count()
    c = (
        Pie(init_opts=opts.InitOpts(bg_color="#E6E6E6"))
        .add("count", [list(z) for z in zip(m.index, m["date"])])
        .set_global_opts(title_opts=opts.TitleOpts(title=f"{year}年{city}空气质量等级分布分析",
                                                   pos_left="center",
                                                   title_textstyle_opts={"color": "black"}),
                         legend_opts=opts.LegendOpts(orient="vertical", pos_right="50px", pos_top="15%"))
    )
    return render_template("contact.html", myechart=c.render_embed())


@app.route('/services.html', methods=["GET", 'POST'])
@login_required
def services():
    if request.method == 'POST':
        city = request.form['city']
        year = request.form['year']
        file_path = f"{city}{year}.csv"
        df = pd.read_csv(file_path, encoding='gbk',
                         names=['date', 'zldj', 'aqi', 'pm25', 'pm10', 'so2', 'no2', 'co', 'o3'])
    else:
        city = "北京"
        year = 2020
        df = pd.read_csv("北京2020.csv", encoding='gbk',
                         names=['date', 'zldj', 'aqi', 'pm25', 'pm10', 'so2', 'no2', 'co', 'o3'])

    v = pd.to_datetime(df['date'], format='%Y/%m/%d')
    df.index = v

    # 计算相关系数
    correlation_coefficient = df['pm25'].corr(df['pm10'])

    def fun(x):
        return x.month

    m = df.groupby(fun)
    y1 = m["pm25"].mean().round(2)
    y2 = m["pm10"].mean().round(2)
    x = [str(i) + "月" for i in range(1, 13)]

    # 生成折线图
    c = (
        Line(init_opts=opts.InitOpts(bg_color="#E6E6E6"))
        .add_xaxis(list(x))
        .add_yaxis("pm2.5", list(y1))
        .add_yaxis("pm10", list(y2))
        .set_global_opts(title_opts=opts.TitleOpts(title=f"{year}年{city}pm2.5和pm10值全年数据分析",
                                                   pos_left="center", pos_top="20px",
                                                   title_textstyle_opts={"color": "black"}),
                         legend_opts=opts.LegendOpts(pos_right="100", pos_top="20px"))
    )

    plt.figure(figsize=(8, 6))
    sns.heatmap(df[['pm25', 'pm10']].corr(), annot=True, cmap='coolwarm', fmt=".2f")
    plt.title('PM2.5&PM10')
    plt.xlabel('pm2.5')
    plt.ylabel('pm10')
    heatmap_path = f"static/images/{city}{year}heatmap.png"
    plt.savefig(heatmap_path)

    return render_template("services.html", myechart=c.render_embed(), correlation_coefficient=correlation_coefficient, city=city, year=year)





if __name__ == '__main__':
    app.run(debug=True)
